package com.webapp.model;

import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

public class AdvertisementModel {
	private Statement stmt = DatabaseConnector.connect();
	
	public boolean createAdvertisement(Advertisement newAdvertisement){
		try{
			while(stmt.executeQuery("SELECT * FROM advertisement WHERE Advertisement_Id = '" +newAdvertisement.getAdvertisementId()+ "'").next()){
				int id = new Double( Math.random() * 10000 ).intValue();
				newAdvertisement.setAdvertisementId(id);
				}
			
				stmt.executeUpdate("INSERT INTO advertisement (Advertisement_Id, Employee, Type, Date, Company, Item_Name, Content, Unit_Price, Availabe_Units) VALUES ('" +
								newAdvertisement.getAdvertisementId()+ "', '" +newAdvertisement.getEmployeeId()+ "', '" +newAdvertisement.getType()+
								"', '" +newAdvertisement.getDate()+ "', '" +newAdvertisement.getCompany()+ "', '" +newAdvertisement.getItemName()+ 
								"', '" +newAdvertisement.getContent()+ "', '" +newAdvertisement.getPrice()+ "', '" +newAdvertisement.getUnits()+ "')");
				return true;
		}catch(Exception ex){return false;}
	}
	
	public boolean deleteAdvertisement(int advertisementId){
		try
		{
			if(stmt.executeQuery("SELECT * FROM advertisement WHERE Advertisement_Id = '" +advertisementId+ "'").next()){
				stmt.executeUpdate("DELETE FROM advertisement WHERE Advertisement_Id = '" +advertisementId+ "'");
				return true;
			}
			else
				return false;
		}
		catch(Exception ex){return false;}
	}
	
	public ArrayList<Advertisement> getAdvertisementsByEmployee(int employeeId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM advertisement WHERE Employee = '" +employeeId+ "'");
			ArrayList<Advertisement> list = new ArrayList<Advertisement>();
			
			while(rs.next()){
				Advertisement advertisement = new Advertisement();
				advertisement.setAdvertisementId(rs.getInt(1));
				advertisement.setEmployeeId(rs.getInt(2));
				advertisement.setType(rs.getString(3));
				advertisement.setDate(rs.getString(4));
				advertisement.setCompany(rs.getString(5));
				advertisement.setItemName(rs.getString(6));
				advertisement.setContent(rs.getString(7));
				advertisement.setPrice(rs.getInt(8));
				advertisement.setUnits(rs.getInt(9));
				list.add(advertisement);
			}
			
			return list;
		}catch(Exception ex){return null;}
	}
	
	public ArrayList<Advertisement> getAllAdvertisements(){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM advertisement");
			ArrayList<Advertisement> list = new ArrayList<Advertisement>();
			
			while(rs.next()){
				Advertisement advertisement = new Advertisement();
				advertisement.setAdvertisementId(rs.getInt(1));
				advertisement.setEmployeeId(rs.getInt(2));
				advertisement.setType(rs.getString(3));
				advertisement.setDate(rs.getString(4));
				advertisement.setCompany(rs.getString(5));
				advertisement.setItemName(rs.getString(6));
				advertisement.setContent(rs.getString(7));
				advertisement.setPrice(rs.getInt(8));
				advertisement.setUnits(rs.getInt(9));
				list.add(advertisement);
			}
			
			return list;
		}catch(Exception ex){return null;}
	}
	
	public Advertisement getAdvertisement(int advertisementId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM advertisement WHERE Advertisement_Id = '" +advertisementId+ "'");
			if(rs.next())
			{
				Advertisement advertisement = new Advertisement();
				advertisement.setAdvertisementId(rs.getInt(1));
				advertisement.setEmployeeId(rs.getInt(2));
				advertisement.setType(rs.getString(3));
				advertisement.setDate(rs.getString(4));
				advertisement.setCompany(rs.getString(5));
				advertisement.setItemName(rs.getString(6));
				advertisement.setContent(rs.getString(7));
				advertisement.setPrice(rs.getInt(8));
				advertisement.setUnits(rs.getInt(9));
				return advertisement;
			}
			else return null;
		}catch(Exception ex){return null;}
	}
	
	public User getUser(int userId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE User_Id = '" +userId+ "'");
			if(rs.next()){
				User use = new User(rs.getInt(1));
				use.setUserId(userId);
				use.setCreationDate(rs.getString(3));
				use.setRating(rs.getInt(4));
				
				rs = stmt.executeQuery("SELECT * FROM persons WHERE SSN = '" +use.getSSN()+ "'");
				rs.next();
				use.setLastName(rs.getString(2));
				use.setFirstName(rs.getString(3));
				use.setAddress(rs.getString(4));
				use.setCity(rs.getString(5));
				use.setState(rs.getString(6));
				use.setZip(rs.getInt(7));
				use.setPhone(rs.getLong(8));
				use.setEmail(rs.getString(9));
				use.setPassword(rs.getString(10));
				
				return use;
			}
			else
				return null;
		}catch(Exception ex){return null;}
	}
	
	public boolean purchaseItem(int adverId, int userId, int numPurchased){
		try{
			Statement stmt1 = DatabaseConnector.connect();
			Statement stmt2 = DatabaseConnector.connect();
			Statement stmt3 = DatabaseConnector.connect();
			ResultSet rs1 = stmt1.executeQuery("SELECT * FROM users WHERE User_Id = '" +userId+ "'");
			ResultSet rs2 = stmt2.executeQuery("SELECT * FROM user_has_account WHERE User_Id = '" +userId+ "'");
			ResultSet rs3 = stmt3.executeQuery("SELECT * FROM advertisement WHERE Advertisement_Id = '" +adverId+ "'");
			
			//Checks If: User Exists && User Has An Account && Advertisement Exists
			if((rs1.next()) && (rs2.next()) && (rs3.next())){
				ResultSet rs = stmt.executeQuery("SELECT Account_Number FROM user_has_account WHERE User_Id = '" +userId+ "'");
				rs.next();
				int accountNum = rs.getInt(1);
				
				DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
				Calendar cal = Calendar.getInstance();
				String date = dateFormat.format(cal.getTime());
				
				//creates a transaction ID, checking if it exists already
				int transId = 0;
				do{
					transId = new Double( Math.random() * 10000000 ).intValue();
					rs = stmt.executeQuery("SELECT * FROM purchases WHERE Transaction_Id = '" +transId+ "'");
				}while(rs.next());
				
				stmt.executeUpdate("INSERT INTO purchases (Transaction_Id, Date, Advertisement, Number_Of_Units, Account) VALUES ('"+
						transId+ "', '" +date+ "', '" +adverId+ "', '" +numPurchased+ "', '" +accountNum+ "')");
				
				return true;
			}
			else 
				return false;
			
		}catch(Exception ex){return false;}
	}
	
}
